package com.headhunter.server.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.headhunter.server.ApplicationRecord;
import com.headhunter.server.Graduate;
import com.headhunter.server.JobData;

public class JobPostingDaoImpl {

	private static String url = "jdbc:mysql://localhost:3306/test";
	private static String user = "root";
	private static String password = "root";
	private static final String tableName = "company_details";

	// TODO: whole company info will ve returned
	public static List<JobData> checkAvailableJobs(Graduate graduate) {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		List<JobData> matches = new LinkedList<JobData>();

		try {
			con = DriverManager.getConnection(url, user, password);
			st = con.createStatement();
			rs = st.executeQuery("SELECT * from " + tableName);

			int companyNo = 0;
			String degree = null;
			String qual = null;
			String companyName = null;
			String companyWebsite = null;

			JobData job = null;

			while (rs.next()) {
				qual = rs.getString(2);
				degree = rs.getString(3);
				if (graduate.getQualifications().containsAll(Arrays.asList(qual.split(",")))
						&& graduate.getDegrees().containsAll(Arrays.asList(degree.split(",")))) {

					companyNo = rs.getInt(1);
					companyName = rs.getString(4);
					companyWebsite = rs.getString(5);

					job = new JobData(companyNo, companyName, companyWebsite);
					job.addDegree(degree);
					job.addQualification(qual);

					matches.add(job);
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (st != null)
					st.close();
				if (con != null)
					con.close();
			} catch (SQLException ex) {
			}
		}
		return matches;
	}

	public static int addJobPosting(ApplicationRecord ar) {
		Connection con = null;
		PreparedStatement pst = null;
		Statement st = null;

		int jobId = -1;

		try {
			con = DriverManager.getConnection(url, user, password);

			st = con.createStatement();
			ResultSet rs = st.executeQuery("SELECT COUNT(*) from " + tableName);

			if (rs.next()) {
				jobId = rs.getInt(1) + 1;
			}
			
			//TODO : UPDATE might be better or dropping and recreating the table
			String query = "INSERT " + tableName + " VALUES(?,?,?,?,?)";
			pst = con.prepareStatement(query);

			pst.setInt(1, jobId);
			
			pst.setString(2, StringUtils.join(ar.getQualifications(), ","));
			pst.setString(3, StringUtils.join(ar.getDegrees(), ","));
			pst.setString(4, ar.getCompanyName());
			pst.setString(5, ar.getCompanyWebsite());

			pst.executeUpdate();
		} catch (SQLException ex) {
		    jobId = -1;
			ex.printStackTrace();
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (st != null)
					st.close();
				if (con != null)
					con.close();
			} catch (SQLException ex) {
			}
		}

		return jobId;
	}
}